﻿#ifndef LITEDBTOOL_H
#define LITEDBTOOL_H

#include "globaltools.h"
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QtSql/QSqlField>
#include <QtSql/QSqlDriver>

struct DatabaseField
{
    enum FieldType
    {
        INTEGER,
        TEXT,
        BLOB
    };

    QString fieldName;
    FieldType fieldType;
    QString constraints;
    const DatabaseField& setData(const QString& name, FieldType type=TEXT, const QString& constraint=QString())
    {
        Q_ASSERT(!name.isEmpty());
        fieldName = name;
        fieldType = type;
        constraints = constraint;
        return *this;
    }
};

struct DatabaseTable
{
    const QString tableName;
    const QString constraints;
    const bool abandonable;
    QVector<DatabaseField> fields;
    DatabaseTable(const QString& name, bool canAbandon = true, const QString& con =QString()):
        tableName(name),constraints(con),abandonable(canAbandon){}
    int fieldIndex(const QString& key)
    {
        for(int i = 0; i < fields.size(); i++)
        {
            if(fields[i].fieldName.compare(key,Qt::CaseInsensitive)==0)
                return i;
        }
        return -1;
    }
};

static QString sqliteEscape(const QString& keyWord, bool simplified = true)
{
    QString key = keyWord;
    if(simplified)
        key = key.simplified();
    key.replace("'", "''");
    return key;
}

static QString toHtmlEscaped(const QString& str)
{
    QString rich;
    const int len = str.length();
    rich.reserve(int(len * 1.1));
    for (int i = 0; i < len; ++i) {
        if (str.at(i) == QLatin1Char('&'))
        {
            if(str.mid(i+1,5)==QLatin1String("quot;"))
            {
                rich += str.mid(i,6);
                i += 5;
            }
            else if(str.mid(i+1,4)==QLatin1String("amp;"))
            {
                rich += str.mid(i,5);
                i += 4;
            }
            else if(str.mid(i+1,3)==QLatin1String("lt;") || str.mid(i+1,3)==QLatin1String("gt;"))
            {
                rich += str.mid(i,4);
                i += 3;
            }
            else
                rich += QLatin1String("&amp;");
        }
        else if (str.at(i) == QLatin1Char('<'))
            rich += QLatin1String("&lt;");
        else if (str.at(i) == QLatin1Char('>'))
            rich += QLatin1String("&gt;");
        else if (str.at(i) == QLatin1Char('"'))
            rich += QLatin1String("&quot;");
        else
            rich += str.at(i);
    }
    rich.squeeze();
    return rich;
}

static bool debug_exec(QSqlQuery& query, const QString& sql)
{
    bool ret = query.exec(sql);
    if(!ret)
        log_debug()<<sql<<ret<<query.lastError().text();
    return ret;
}

static bool debug_exec(QSqlQuery& query)
{
    bool ret = query.exec();
    if(!ret)
        log_debug()<<query.lastQuery()<<ret<<query.lastError().text();
    return ret;
}

static void create_table(QSqlDatabase& database, const DatabaseTable& table)
{
    int count = table.fields.size();
    if(count<=0)
        return;

    QStringList fields;
    for (int i = 0; i < count; i++)
    {
        const DatabaseField& field = table.fields[i];
        if(field.fieldName.isEmpty())
            continue;
        QString fst = field.fieldName;
        if(field.fieldType == DatabaseField::INTEGER)
            fst += " INTEGER";
        else if(field.fieldType == DatabaseField::TEXT)
            fst += " TEXT";
        else if(field.fieldType == DatabaseField::BLOB)
            fst += " BLOB";
        else
            continue;
        if(!field.constraints.isEmpty())
            fst += ' '+field.constraints;
        fields<<fst;
    }
    if(fields.isEmpty())
        return;
    QString createStatement = "create table "+table.tableName+" (" + fields.join(", ") + ')';

    QSqlQuery query(database);

    QString sql = "select sql from sqlite_master where type='table' and name='"+table.tableName+"'";
    if(debug_exec(query,sql) && query.next())
    {
        QString oldsql = query.record().value(0).toString();
        if(oldsql.compare(createStatement, Qt::CaseInsensitive)==0)
            return;
        log_debug()<<createStatement;
        log_debug()<<query.record().value(0).toString();
        if(!table.abandonable)
        {
            QSet<QString> fieldList;
            //parse oldone
            QRegExp re("\\((.[^\\(\\)]*)\\)");
            if(re.indexIn(oldsql)>=0)
            {
                QString str = re.cap(0);
                str.remove(0,1).chop(1);
                QStringList strList = str.simplified().split(',',QString::SkipEmptyParts);
                foreach (const QString& s, strList)
                {
                    fieldList << s.section(' ', 0, 0, QString::SectionSkipEmpty).toLower();
                }
            }
            //find common
            QSet<QString> fields;
            foreach (const DatabaseField& field, table.fields)
            {
                fields<<field.fieldName.trimmed().toLower();
            }
            fieldList.intersect(fields);
            if(fieldList.size()>0)
            {
                //rename oldone
                QString bkname = table.tableName+"backup"+QString::number(QDateTime::currentMSecsSinceEpoch());
                sql = "ALTER TABLE "+table.tableName+" RENAME TO "+bkname;
                if(debug_exec(query,sql))
                {
                    //create newone
                    if(debug_exec(query,createStatement))
                    {
                        //insert from oldone
                        QString cols = fieldList.toList().join(',');
                        sql = "INSERT INTO "+table.tableName+'('+cols+") SELECT "+cols+" FROM "+bkname;
                        debug_exec(query,sql);
                        //drop oldone
                        sql = "DROP TABLE "+bkname;
                        debug_exec(query,sql);
                    }
                    else
                    {
                        sql = "ALTER TABLE "+bkname+" RENAME TO "+table.tableName;
                        if(debug_exec(query,sql))
                            return;
                    }
                }
                return;
            }
        }
        sql = "drop table "+table.tableName;
        debug_exec(query,sql);
    }

    debug_exec(query,createStatement);
}

bool query_table(QSqlQuery& query, const DatabaseTable& table, const QString& extra = QString())
{
    if(table.tableName.isEmpty())
        return false;
    QStringList names;
    foreach (const DatabaseField& field, table.fields)
    {
        names<<field.fieldName;
    }
    QString statement = "select " + names.join(',') + " from " + table.tableName;
    if(!extra.isEmpty())
    {
        if(!extra.startsWith(' '))
            statement.append(' ');
        statement.append(extra);
    }
    return debug_exec(query,statement);
}

bool query_table(QSqlQuery& query, const QString& tableName, const QString& extra = QString())
{
    if(tableName.isEmpty())
        return false;
    QString statement = "select * from " + tableName;
    if(!extra.isEmpty())
    {
        if(!extra.startsWith(' '))
            statement.append(' ');
        statement.append(extra);
    }
    return debug_exec(query,statement);
}

void sqlRecordValues(QSqlQuery& query, QMap<QString,QVariant>& sqlResult)
{
    QSqlRecord record = query.record();
    sqlResult.clear();

    int fieldCount = record.count();
    for(int i = 0; i<fieldCount; i++)
        sqlResult[record.fieldName(i)]=record.value(i);
}

bool prepareInsert(QSqlQuery& query, const DatabaseTable& table, const QVariantList& vars, bool replace = true)
{
    int fsize = table.fields.size();
    int vsize = vars.size();
    if(vsize>fsize || vsize==0)
        return false;
    QString statement = "insert ";
    if(replace)
        statement += "or replace ";
    statement +=  "into " + table.tableName + " values (" + QString("?,").repeated(fsize-1) + "?)";
    if(query.prepare(statement))
    {
        int i = 0;
        foreach (const QVariant& v, vars)
        {
            const DatabaseField& f = table.fields[i++];
            if(f.fieldType == DatabaseField::BLOB)
                query.addBindValue(v, QSql::In | QSql::Binary);
            else
                query.addBindValue(v);
        }
        while(i++ < fsize)
            query.addBindValue(QVariant());
        return true;
    }
    log_debug()<<statement;
    return false;
}

class ImContactTbl : public DatabaseTable
{
public:
    const QString uid;
    const QString info;
    ImContactTbl():
        DatabaseTable("im_contact"),
        uid("uid"),
        info("info")
    {
        DatabaseField field;
        fields<<field.setData(uid,DatabaseField::INTEGER,"PRIMARY KEY");
        fields<<field.setData(info,DatabaseField::BLOB);
        fields.squeeze();
    }
};
const ImContactTbl gImContactTbl;

class ChatRoomTbl : public DatabaseTable
{
public:
    const QString rid;
    const QString rname;
    const QString rstyle;
    const QString updtime;
    const QString silent;
    ChatRoomTbl():
        DatabaseTable("chat_room_v3"),
        rid("rid"),
        rname("rname"),
        rstyle("rstyle"),
        updtime("updtime"),
        silent("silent")
    {
        DatabaseField field;
        fields<<field.setData(rid,DatabaseField::INTEGER,"PRIMARY KEY");
        fields<<field.setData(rname,DatabaseField::TEXT);
        fields<<field.setData(rstyle,DatabaseField::INTEGER);
        fields<<field.setData(updtime,DatabaseField::INTEGER);
        fields<<field.setData(silent,DatabaseField::INTEGER);
        fields.squeeze();
    }
};
const ChatRoomTbl gChatRoomTbl;

class SessionTbl : public DatabaseTable
{
public:
    const QString idtype;
    const QString name;
    const QString timestamp;
    const QString style;
    SessionTbl():
        DatabaseTable("session_v3"),
        idtype("idtype"),
        name("name"),
        timestamp("timestamp"),
        style("style")
    {
        DatabaseField field;
        fields<<field.setData(idtype,DatabaseField::TEXT,"PRIMARY KEY");
        fields<<field.setData(name,DatabaseField::TEXT);
        fields<<field.setData(timestamp,DatabaseField::INTEGER);
        fields<<field.setData(style,DatabaseField::INTEGER);
        fields.squeeze();
    }
};
const SessionTbl gSessionTbl;

class ChatRecordTbl : public DatabaseTable
{
public:
    static const QString basicName;
    static const QString mId;
    static const QString fromId;
    static const QString toId;
    static const QString secret;
    static const QString html;
    static const QString plain;
    static const QString time;
    static const QString step;
    ChatRecordTbl(const QString& suffix, bool crowd):
        DatabaseTable(basicName+suffix, false)
    {
        DatabaseField field;
        fields<<field.setData(mId,DatabaseField::TEXT,"PRIMARY KEY");
        fields<<field.setData(fromId,DatabaseField::TEXT,"NOT NULL");
        fields<<field.setData(html,DatabaseField::TEXT,"NOT NULL");
        fields<<field.setData(plain,DatabaseField::TEXT,"NOT NULL");
        fields<<field.setData(time,DatabaseField::INTEGER,"NOT NULL");
        fields<<field.setData(step,DatabaseField::INTEGER,"NOT NULL");
        if(crowd)
        {
            fields<<field.setData(toId,DatabaseField::TEXT);
            fields<<field.setData(secret,DatabaseField::INTEGER);
        }
        fields.squeeze();
    }
};
const QString ChatRecordTbl::basicName = "chat_record_v3_";
const QString ChatRecordTbl::mId = "mId";
const QString ChatRecordTbl::fromId = "fromId";
const QString ChatRecordTbl::toId = "toId";
const QString ChatRecordTbl::secret = "secret";
const QString ChatRecordTbl::html = "html";
const QString ChatRecordTbl::plain = "plain";
const QString ChatRecordTbl::time = "time";
const QString ChatRecordTbl::step = "step";
std::map<QString,ChatRecordTbl> gChatRecordTbl;

#endif // LITEDBTOOL_H
